﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace HRdex_Final.Forms
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
        DataTable dt = new DataTable("dt");

        //Session String
        string SessionID()
        {
            return Session["EmployeeID"].ToString();
        }



        protected void Page_Load(object sender, EventArgs e)
        {
            string formCBAID = Request.QueryString["formCBAID"];

            ddlStatus.Visible = false;
            SqlCommand cmd3 = new SqlCommand("SELECT  EmployeeID, LastName + ', ' + FirstName + ' ' + MiddleName AS Name, catPositionsID, DeptID, CollegeID, UserLevelID FROM formEmployees WHERE (EmployeeID=" + SessionID() + ")", conn);
            SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
            DataTable dt3 = new DataTable("dt3");

            
            da3.Fill(dt3);
            txtEmpNo.Text = dt3.Rows[0].ItemArray[0].ToString();
            txtEmpName.Text = dt3.Rows[0].ItemArray[1].ToString();
            ddlDept.SelectedValue = dt3.Rows[0].ItemArray[3].ToString();
            ddlCollege.SelectedValue = dt3.Rows[0].ItemArray[4].ToString();
            txtEmpNo.Enabled = false;
            txtEmpName.Enabled = false;
            ddlDept.Enabled = false;
            ddlCollege.Enabled = false;
            lblHR.Visible = false;
            txtDiscount.Enabled = false;

            txtCBAid.Visible = false;
            btnDownload.Visible = false;
            lblDate.Text = "Date";
            lblDateResult.Text = Convert.ToString(DateTime.Now);
            txtAppHR.Visible = false;

            Selected();

        }

        //Submit
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string formCBAID = Request.QueryString["formCBAID"];

            if (txtCBAid.Text != formCBAID)
            {
                try
                {
                    SqlCommand cmdd = new SqlCommand("INSERT Into formCBA (StudentNo, DateSubmitted, CourseID, Year, StudentName, " +
                        "EmployeeID, DepartmentID, CollegeID,  pDiscount, OrdinalPos,Cleared,Enrollment, Units, AttachmentData, AttachmentName )" +
                        "VALUES (@sNo,@date,@courseID, @year, @sName,@empID,@deptID, @collegeID, @pDiscount, @posID, @cleared,@enrollment, " +
                        "@units, @data, @dataname)", conn);

                    Stream fs = fuAttachment.PostedFile.InputStream;
                    BinaryReader br = new BinaryReader(fs);
                    Byte[] bytes = br.ReadBytes((Int32)fs.Length);

                    string filepath = fuAttachment.PostedFile.FileName;
                    string filename = Path.GetFileName(filepath);

                    SqlDataAdapter daa = new SqlDataAdapter(cmdd);

                    conn.Open();
                    cmdd.Parameters.Add("@sNo", SqlDbType.Int).Value = txtStudentNo.Text;
                    cmdd.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Now;
                    cmdd.Parameters.Add("@courseID", SqlDbType.Int).Value = dllCourse.SelectedValue;
                    cmdd.Parameters.Add("@year", SqlDbType.Int).Value = ddlYear.SelectedValue;
                    cmdd.Parameters.Add("@sName", SqlDbType.VarChar).Value = ddlStudent.Text;
                    cmdd.Parameters.Add("@empID", SqlDbType.Int).Value = txtEmpNo.Text;
                    cmdd.Parameters.Add("@deptID", SqlDbType.Int).Value = ddlDept.SelectedValue;
                    cmdd.Parameters.Add("@collegeID", SqlDbType.Int).Value = ddlCollege.SelectedValue;
                    cmdd.Parameters.Add("@pDiscount", SqlDbType.VarChar).Value = txtDiscount.Text;
                    cmdd.Parameters.Add("@posID", SqlDbType.Int).Value = txtOrdinalPos.Text;
                    cmdd.Parameters.Add("@cleared", SqlDbType.VarChar).Value = txtCleared.Text;
                    cmdd.Parameters.Add("@enrollment", SqlDbType.VarChar).Value = txtEnrollment.Text;
                    cmdd.Parameters.Add("@units", SqlDbType.VarChar).Value = txtUnits.Text;
                    cmdd.Parameters.Add("@data", SqlDbType.VarBinary).Value = bytes;
                    cmdd.Parameters.Add("@dataname", SqlDbType.VarChar).Value = filename;

                    cmdd.ExecuteNonQuery();
                    conn.Close();

                    lblConfirmation.Text = "You successfully add new CBA request. Please wait upon your approval upon reviewing your application. Thank you!";

                    //submit_notify(slct_hr());

                    txtCBAid.Text = null;
                    txtCleared.Text = null;
                    txtDiscount.Text = null;
                    txtEmpName.Text = null;
                    txtEmpNo.Text = null;
                    txtEnrollment.Text = null;

                    txtOrdinalPos.Text = null;
                    txtStudentNo.Text = null;
                    txtUnits.Text = null;
                    btnClear.Visible = true;
                    btnSubmit.Visible = true;
                    txtStudentNo.Enabled = false;
                    dllCourse.Enabled = false;
                    txtOrdinalPos.Enabled = false;
                }

                catch (Exception)
                {

                    //throw;
                }
            }
            else
            {
                Edit();
                ddlStatus.Enabled = false;
                //notification();
            }
        }

        public void Selected()
        {
            string formCBAID = Request.QueryString["formCBAID"];

            if (formCBAID != null)
            {

                txtCBAid.Enabled = false;
                txtCleared.Enabled = false;
                txtEmpNo.Enabled = false;
                txtEnrollment.Enabled = false;
                ddlStudent.Enabled = false;
                txtDiscount.Enabled = false;
                ddlStatus.Enabled = false;
                txtStudentNo.Enabled = false;
                txtUnits.Enabled = false;
                dllCourse.Visible = false;
                fuAttachment.Visible = false;
                btnClear.Visible = false;
                RequiredFieldValidator14.Enabled = false;
                txtOrdinalPos.Enabled = false;
                ddlDept.Enabled = false;
                HyperLink1.Visible = false;
                //Approval
                lblHR.Visible = true;
                ddlCollege.Enabled = false;
                dllCourse.Enabled = false;
                ddlYear.Enabled = false;
                Button2.Visible = false;    
                btnSubmit.Visible = false;
                txtStudentNo.Enabled = false;
                dllCourse.Enabled = false;
                ddlYear.Enabled = false;
                txtOrdinalPos.Enabled = false;
                ddlYear.Enabled = true;
                SqlCommand cmd5 = new SqlCommand("SELECT formCBA.formCBAID, formCBA.StudentNo, formCBA.DateSubmitted," +
                    "catCourse.CourseName, formCBA.Year, formCBA.StudentName, formCBA.pDiscount," +
                    "formCBA.Cleared, formCBA.Enrollment, formCBA.Units, formEmployees.EmployeeID, " +
                    "formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, " +
                    "formCBA.DepartmentID, formCBA.CollegeID, formCBA.OrdinalPos, formCBA.AttachmentName,  formCBA.HRApproval FROM formCBA INNER JOIN catCourse ON formCBA.CourseID = catCourse.CourseID INNER JOIN formEmployees ON formCBA.EmployeeID = formEmployees.EmployeeID INNER JOIN catDepartment ON formCBA.DepartmentID = catDepartment.DeptID INNER JOIN catCollege ON formCBA.CollegeID = catCollege.CollegeID WHERE (formCBA.formCBAID =" + formCBAID + ")", conn);
                SqlDataAdapter da5 = new SqlDataAdapter(cmd5);
                DataTable dt5 = new DataTable("dt5");
                try
                {
                    da5.Fill(dt5);

                    if (dt5.Rows[0].ItemArray[15].ToString() != null)
                    {
                        btnDownload.Visible = true;
                    }
                    else
                    {
                        btnDownload.Visible = false;
                    }
                    
                    txtCBAid.Text = dt5.Rows[0].ItemArray[0].ToString();
                    txtStudentNo.Text = dt5.Rows[0].ItemArray[1].ToString();
                    lblDateResult.Text = dt5.Rows[0].ItemArray[2].ToString();
                    dllCourse.SelectedValue = dt5.Rows[0].ItemArray[3].ToString();
                    ddlYear.SelectedValue = dt5.Rows[0].ItemArray[4].ToString();
                    ddlStudent.SelectedValue = dt5.Rows[0].ItemArray[5].ToString();
                    txtDiscount.Text = dt5.Rows[0].ItemArray[6].ToString();
                    txtCleared.Text = dt5.Rows[0].ItemArray[7].ToString();
                    txtEnrollment.Text = dt5.Rows[0].ItemArray[8].ToString();
                    txtUnits.Text = dt5.Rows[0].ItemArray[9].ToString();
                    txtEmpNo.Text = dt5.Rows[0].ItemArray[10].ToString();
                    txtEmpName.Text = dt5.Rows[0].ItemArray[11].ToString();
                    ddlDept.SelectedValue = dt5.Rows[0].ItemArray[12].ToString();
                    ddlCollege.SelectedValue = dt5.Rows[0].ItemArray[13].ToString();
                    txtOrdinalPos.Text = dt5.Rows[0].ItemArray[14].ToString();
                    txtAppHR.Text = dt5.Rows[0].ItemArray[16].ToString();
                    ddlYear.Enabled = false;
                }
                catch (Exception)
                {


                }

                   

                SqlCommand cmd2 = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName, " +
                    "catPositions.catPositionsName, catDepartment.DepartmentName FROM formEmployees INNER JOIN " +
                    "catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN " +
                    "catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID INNER JOIN catDepartment " +
                    "ON formEmployees.DeptID = catDepartment.DeptID WHERE (formEmployees.EmployeeID = " + SessionID() + ")", conn);
                SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
                DataTable dt2 = new DataTable("dt2");
                try
                {
                    da2.Fill(dt2);
                    if (dt2.Rows[0].ItemArray[1].ToString() == "Admin" || dt2.Rows[0].ItemArray[1].ToString() == "SubAdmin" && dt2.Rows[0].ItemArray[2].ToString() == "Head" && dt2.Rows[0].ItemArray[3].ToString() == "HR Salaries and Benefits")
                    {
                        child.SelectCommand = "SELECT CBAmonID, OrdinalPositionEnrolled, StudentNo, CourseID, YearID, EmployeeID, StudentFirstName + ' ' + StudentLastName AS [Student Name] FROM CBAmon";

                        ddlStatus.Visible = true;
                        btnSubmit.Visible = true;
                        txtAppHR.Visible = false;
                         
                      

                        if (dt5.Rows[0].ItemArray[16].ToString() == "Approved")
                        {
                            ddlStatus.SelectedValue = dt5.Rows[0].ItemArray[16].ToString();
                            btnSubmit.Enabled = false;
                        }
                        else if (dt5.Rows[0].ItemArray[16].ToString() == "Denied")
                        {
                            ddlStatus.SelectedValue = dt5.Rows[0].ItemArray[16].ToString();
                            btnSubmit.Enabled = false;
                        }
                        else
                        {
                            ddlStatus.Enabled = true;
                        }
                    }
                    else
                    {
                        ddlStatus.SelectedValue = dt5.Rows[0].ItemArray[16].ToString();
                        ddlStudent.Enabled = false;
                        ddlStatus.Visible = true;
                    }
                }
                catch (Exception)
                {


                }

                

            }
        }

        public void Edit()
        {
            string formCBAID = Request.QueryString["formCBAID"];
            SqlCommand cmd7 = new SqlCommand("UPDATE formCBA SET  HRApproval = @status WHERE(formCBAID = " + formCBAID + ")", conn);
            SqlDataAdapter da7 = new SqlDataAdapter(cmd7);
            conn.Open();

            cmd7.Parameters.AddWithValue("@status", SqlDbType.VarChar).Value = ddlStatus.SelectedValue;
            try
            {
                cmd7.ExecuteNonQuery();
            }
            catch (Exception)
            {


            }

            conn.Close();

            lblConfirmation.Text = "You successfully put remarks!";

            notification(ddlStatus.SelectedValue);


            ddlStatus.Enabled = false;
        }

        void notification(string result)
        {
            string formCBAID = Request.QueryString["formCBAID"];
            string desc = "Your request for CBA has been " + result + "by the HR";
            string sqlc = "INSERT INTO formMessegeSender " +
                "(Subject, Description, DateTime, EmployeeID, catMessegeID, fromEmpID, Readed) " +
                "VALUES ('[CBA Request]','" + desc + "','" + DateTime.Now + "'," + cbasender(formCBAID) + ",2," + SessionID() + ",0)";
            SqlCommand cmd = new SqlCommand(sqlc, conn);

            conn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {

                throw;
            }

            conn.Close();
        }

        string cbasender(string src)
        {
            string sqlx = "SELECT EmployeeID FROM formCBA WHERE formCBAID = " + src;
            SqlDataAdapter cmd = new SqlDataAdapter(sqlx, conn);
            DataTable tbl = new DataTable();

            conn.Open();
            cmd.Fill(tbl);
            conn.Close();

            return tbl.Rows[0][0].ToString();
        }

        protected void btnClear_Click(object sender, EventArgs e)
        {
            txtCBAid.Text = null;
            txtCleared.Text = null;
            txtDiscount.Text = null;
            txtEmpName.Text = null;
            txtEmpNo.Text = null;
            txtEnrollment.Text = null;
            ddlStudent.Text = null;
            txtOrdinalPos.Text = null;
            txtStudentNo.Text = null;
            txtUnits.Text = null;
        }

        protected void btnAddChildren_Click(object sender, EventArgs e)
        {
            Response.Redirect("~/Forms/CBAmonitoring.aspx");
        }

        protected void btnDownload_Click(object sender, EventArgs e)
        {
            string msg = Request.QueryString["formCBAID"];
            string strQuery = "SELECT AttachmentData, AttachmentName FROM formCBA WHERE formCBAID = " + msg;
            SqlCommand cmd = new SqlCommand(strQuery);
            DataTable dt = GetData(cmd);
            if (dt != null)
            {
                download(dt);
            }
        }

        private DataTable GetData(SqlCommand cmd)
        {
            DataTable dt = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
            try
            {
                conn.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                return dt;

            }
            catch
            {
                return null;
            }
            finally
            {
                conn.Close();
                sda.Dispose();
                conn.Dispose();
            }

        }
        private void download(DataTable dt)
        {
            Byte[] bytes = (Byte[])dt.Rows[0]["AttachmentData"];
            Response.Buffer = true;
            Response.Charset = "";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            //Response.ContentType = dt.Rows[0]["FileType"].ToString();
            Response.AddHeader("content-disposition", "attachment;FileName="
            + dt.Rows[0]["AttachmentName"].ToString());
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {

            string formCBAID = Request.QueryString["formCBAID"];
            SqlCommand cmdCBA = new SqlCommand("SELECT CBAmonID, OrdinalPositionEnrolled, StudentNo, CourseID, YearID, EmployeeID FROM CBAmon WHERE CBAmonID= "+ ddlStudent.SelectedValue, conn);
            SqlDataAdapter daCBA = new SqlDataAdapter(cmdCBA);
            DataTable dtCBA = new DataTable("dtCBA");
                daCBA.Fill(dtCBA);

                txtOrdinalPos.Text = dtCBA.Rows[0].ItemArray[1].ToString();
                txtStudentNo.Text = dtCBA.Rows[0].ItemArray[2].ToString();
                dllCourse.SelectedValue = dtCBA.Rows[0].ItemArray[3].ToString();

                if (Convert.ToInt16(txtOrdinalPos.Text) <= 2 && Convert.ToInt16(txtOrdinalPos.Text) >= 1)
                {
                    txtDiscount.Text = "100% Scholar";
                }
                else if (Convert.ToInt16(txtOrdinalPos.Text) <= 4 && Convert.ToInt16(txtOrdinalPos.Text) >= 3)
                {
                    txtDiscount.Text = "75% Scholar";
                }
                else
                {
                    txtDiscount.Text = " ";
                }
                

        }


    }
}
